﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
using System.Diagnostics;

namespace WowRaidListTracker.DAL
{
    public class ListTrackerDal
    {
        public static int SQLITE_OK = 0;

        private SQLiteCommand liteCmd;
        private SQLiteConnection liteCon;
        private SQLiteConnectionStringBuilder liteSb;
        private SQLiteTransaction liteTrans;

        private string _dbPath;
        private string _dbName;

        public ListTrackerDal(string dbPath, string dbName)
        {
            _dbPath = dbPath;
            _dbName = dbName;
            liteSb = new SQLiteConnectionStringBuilder();
            liteCon = new SQLiteConnection();
            liteCmd = new SQLiteCommand();
            liteSb.Add("data source", dbPath + dbName);
            liteCon.ConnectionString = liteSb.ToString();
            liteCmd.Connection = liteCon;
        }

        public ListTrackerDal()
        {
            try
            {
                _dbPath = string.Empty;
                _dbName = string.Empty;
                liteSb = new SQLiteConnectionStringBuilder();
                liteCon = new SQLiteConnection();
                liteCmd = new SQLiteCommand();
                string source = ConfigurationManager.ConnectionStrings["WowRaidListTracker.DAL.Properties.Settings.sqliteConnection"].ToString();
                liteCon.ConnectionString = source;
                liteCmd.Connection = liteCon;
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.Message);
            }
        }

        public object Execute(string query, SQLiteQueryType type)
        {
            return Execute(query, type, new List<SQLiteParameter>());
        }

        public object Execute(string query, SQLiteQueryType type, List<SQLiteParameter> parameters)
        {
            if (query == null || query.Length < 1)
                throw new ArgumentException("The query string must not be null or blank");

            try
            {
                liteCon.Open();
                liteCmd.CommandText = query;
                liteCmd.CommandType = CommandType.Text;
                AddParameters(parameters);

                object data = null;

                switch (type)
                {
                    case SQLiteQueryType.CREATE:
                        ExecuteCreate();
                        data = ListTrackerDal.SQLITE_OK;
                        break;
                    case SQLiteQueryType.NONSTANDARDQUERY:
                        data = ExecuteInsertOrUpdate();
                        break;
                    case SQLiteQueryType.STANDARDQUERY:
                        data = ExecuteStandardQuery();
                        break;
                }
                liteCon.Close();
                return data;
            }
            catch (SQLiteException sle)
            {
                liteCon.Close();
                throw new SQLiteException("There was a database error.", sle);
            }
            finally
            {
                liteCon.Close();
            }
        }

        private void ExecuteCreate()
        {
            liteCmd.ExecuteScalar();
        }

        private DataTable ExecuteStandardQuery()
        {
            SQLiteDataReader liteReader = liteCmd.ExecuteReader();
            DataTable dt = new DataTable();
            List<string> columnNames = new List<string>();
            if (liteReader.HasRows)
            {
                for (int i = 0, n = liteReader.FieldCount; i < n; i++)
                {
                    DataColumn dcCol = new DataColumn(liteReader.GetName(i));
                    if (liteReader.GetValue(i).GetType() != typeof(DBNull))
                        dcCol.DataType = liteReader.GetValue(i).GetType();
                    columnNames.Add(dcCol.ColumnName);
                    dt.Columns.Add(dcCol);
                }

                while (liteReader.Read())
                {
                    DataRow dr = dt.NewRow();
                    foreach (string s in columnNames)
                    {
                        dr[s] = liteReader[s];
                    }
                    dt.Rows.Add(dr);
                }
            }
            liteReader.Close();
            return dt;
        }

        private NonQueryResult ExecuteInsertOrUpdate()
        {
            return new NonQueryResult(liteCmd.ExecuteNonQuery(), 0);
        }

        private void AddParameters(List<SQLiteParameter> parameters)
        {
            liteCmd.Parameters.Clear();
            foreach (SQLiteParameter p in parameters)
            {
                liteCmd.Parameters.Add(p);
            }
        }

        public void BeginTransaction()
        {
            liteTrans = liteCon.BeginTransaction();
        }

        public void CommitTransaction()
        {
            liteTrans.Commit();
        }

        public void RollbackTransaction()
        {
            liteTrans.Rollback();
        }
    }

    public enum SQLiteQueryType
    {
        STANDARDQUERY,
        NONSTANDARDQUERY,
        CREATE
    }
}
